package com.adinall.dsp.service.impl;

import java.text.DecimalFormat;
import java.text.Format;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.ui.Model;

import com.adinall.dsp.common.data.RPTBannerData;
import com.adinall.dsp.common.data.RPTData;
import com.adinall.dsp.common.data.RPTHourData;
import com.adinall.dsp.common.data.RPTLocationData;
import com.adinall.dsp.common.data.RPTMediaData;
import com.adinall.dsp.common.enums.RPTSort;
import com.adinall.dsp.common.enums.Sort;
import com.adinall.dsp.core.data.Page;
import com.adinall.dsp.core.globle.Constants;
import com.adinall.dsp.core.globle.Selector;
import com.adinall.dsp.core.poi.excel.Excel2007Write;
import com.adinall.dsp.core.poi.excel.Write;
import com.adinall.dsp.core.utils.CheckUtils;
import com.adinall.dsp.core.utils.DateUtils;
import com.adinall.dsp.core.utils.NumberUtils;
import com.adinall.dsp.db.entity.RPTAdsEntity;
import com.adinall.dsp.db.entity.RPTOrderBannerEntity;
import com.adinall.dsp.db.entity.RPTOrderHourEntity;
import com.adinall.dsp.db.entity.RPTOrderLocationEntity;
import com.adinall.dsp.db.entity.RPTOrderMediaEntity;
import com.adinall.dsp.service.AccountService;
import com.adinall.dsp.service.DownloadBean;
import com.adinall.dsp.service.FileService;
import com.adinall.dsp.service.RPTAdgroupService;
import com.adinall.dsp.service.form.RPTAdgroupForm;
import com.adinall.dsp.service.form.RPTOrderDimensionForm;
import com.cloudcross.ssp.base.dao.GenericIBatisDao;
import com.cloudcross.ssp.common.utils.PropertiesUtils;
import com.cloudcross.ssp.model.Account;
import com.cloudcross.ssp.model.AdGroup;

/**
 * 策略统计用Service
 * 
 * @author 高万超
 * @version V1.0.0 $ 2017年9月11日 下午5:07:05
 */
@Service
public class RPTAdgroupServiceImpl implements RPTAdgroupService {

    private final static String MAPPER_KEY = "rpt-adgroup";
    private final static String MAPPER_KEY_RPT_ADS_DAY = "rpt-ads-day";
    private final static String MAPPER_KEY_RPT_ADGROUP_DAY = "rpt-adgroup-daily";

    @Autowired
    private GenericIBatisDao dao;

    @Autowired
    private AccountService accountService;

    @Autowired
    private FileService fileService;

    public void detailInit(Model model, Integer id) {

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return;
        }

        model.addAttribute("account_type", account.getType());
        model.addAttribute("entity", dao.get(MAPPER_KEY + ".get", id));
        model.addAttribute("selector_date", Selector.date());
    }

    public Page<RPTData> list(RPTAdgroupForm form) {

        Page<RPTData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        form.setAgentId(account.getAgentId());
        form.setAdvertiserId(account.getAdvertiserId());

        if (CheckUtils.isNull(form.getSortColumn())) {
            form.setSortColumn(RPTSort.DEF.code);
            form.setSortType(Sort.DESC.code);
        }

        int count = dao.count(MAPPER_KEY + ".count", form);

        if (count == 0) {
            return page;
        }

        page.setCount(count);
        page.execute();

        List<RPTAdsEntity> data = dao.getList(MAPPER_KEY + ".list", form);

        if (CheckUtils.isEmpty(data)) {
            return page;
        }

        for (RPTAdsEntity entity : data) {

            RPTData tmp = new RPTData();
            tmp.setId(entity.getId());
            tmp.setName(entity.getName());
            tmp.setReq(entity.getRequests());
            tmp.setShow(entity.getShows());
            tmp.setClick(entity.getClicks());
            tmp.setClick_rate(entity.getClick_rate());
            tmp.setEcpm(entity.getEcpm());
            tmp.setRevenue(entity.getRevenues());

            page.getData().add(tmp);
        }

        return page;
    }

    public RPTData all(RPTOrderDimensionForm form) {

        AdGroup adgroup = dao.get(MAPPER_KEY + ".get", form.getId());

        if (CheckUtils.isNull(adgroup)) {
            return new RPTData();
        }

        RPTData data = new RPTData();

        data.setId(adgroup.getId());
        data.setName(adgroup.getAdGroupName());

        RPTAdsEntity entity = dao.get(MAPPER_KEY_RPT_ADS_DAY + ".rptByAdgroupId", form);

        if (!CheckUtils.isNull(entity)) {

            data.setReq(entity.getRequests());
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());

            if (data.getShow() != 0) {
                data.setClick_rate(entity.getClicks() * 1.0 / entity.getShows());
                data.setEcpm(entity.getRevenues() * 1000.0 / entity.getShows());
            }
        }

        return data;
    }

    public Page<RPTData> daily(RPTOrderDimensionForm form) {

        Page<RPTData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        if (CheckUtils.isNull(form.getSortColumn())) {
            form.setSortColumn(RPTSort.DEF.code);
            form.setSortType(Sort.DESC.code);
        }

        int count = dao.count(MAPPER_KEY_RPT_ADGROUP_DAY + ".count", form);

        if (count == 0) {
            return page;
        }

        page.setCount(count);
        page.execute();

        List<RPTAdsEntity> list = dao.getList(MAPPER_KEY_RPT_ADGROUP_DAY + ".list", form);

        if (CheckUtils.isEmpty(list)) {
            return page;
        }

        for (RPTAdsEntity entity : list) {

            RPTData data = new RPTData();

            data.setId(entity.getId());
            data.setDate(entity.getDate());
            data.setReq(entity.getRequests());
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());
            data.setClick_rate(entity.getClick_rate());
            data.setEcpm(entity.getEcpm());

            page.getData().add(data);
        }

        return page;
    }

    public Page<RPTLocationData> location(RPTOrderDimensionForm form) {

        Page<RPTLocationData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        if (CheckUtils.isNull(form.getSortColumn())) {
            form.setSortColumn(RPTSort.DEF.code);
            form.setSortType(Sort.DESC.code);
        }

        int count = dao.count("rpt-adgroup-location.count", form);

        if (count == 0) {
            return page;
        }

        page.setCount(count);
        page.execute();

        List<RPTOrderLocationEntity> list = dao.getList("rpt-adgroup-location.list", form);

        if (CheckUtils.isEmpty(list)) {
            return page;
        }

        for (RPTOrderLocationEntity entity : list) {

            RPTLocationData data = new RPTLocationData();

            data.setDate(entity.getDate());
            data.setId(entity.getId());
            data.setProvince(entity.getProvince());
            data.setCity(entity.getCity());
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());
            data.setEcpm(entity.getEcpm());
            data.setClick_rate(entity.getClick_rate());

            page.getData().add(data);
        }

        return page;
    }

    public Page<RPTHourData> hour(RPTOrderDimensionForm form) {

        Page<RPTHourData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        if (CheckUtils.isEmpty(form.getEndTime())) {

            Calendar c = Calendar.getInstance();
            c.add(Calendar.DAY_OF_MONTH, -1);
            form.setEndTime(DateUtils.format(c.getTime(), DateUtils.PATTERN_yyyy_MM_dd));
        }

        int count = dao.count("rpt-adgroup-hour.count", form);

        if (count == 0) {
            return page;
        }

        if (CheckUtils.isNull(form.getSortColumn())) {
            form.setSortColumn(RPTSort.DEF.code);
            form.setSortType(Sort.DESC.code);
        }

        page.setCount(count);
        page.execute();

        List<RPTOrderHourEntity> list = dao.getList("rpt-adgroup-hour.list", form);

        if (CheckUtils.isEmpty(list)) {
            return page;
        }

        Format format = new DecimalFormat("00");

        for (RPTOrderHourEntity entity : list) {

            RPTHourData data = new RPTHourData();

            data.setId(entity.getId());
            data.setDate(entity.getDate());
            data.setHour(format.format(entity.getHour()));
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());
            data.setEcpm(entity.getEcpm());
            data.setClick_rate(entity.getClick_rate());

            page.getData().add(data);
        }

        return page;
    }

    public Page<RPTMediaData> media(RPTOrderDimensionForm form) {

        Page<RPTMediaData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        if (CheckUtils.isNull(form.getSortColumn())) {
            form.setSortColumn(RPTSort.DEF.code);
            form.setSortType(Sort.DESC.code);
        }

        int count = dao.count("rpt-adgroup-media.count", form);

        if (count == 0) {
            return page;
        }

        page.setCount(count);
        page.execute();

        List<RPTOrderMediaEntity> list = dao.getList("rpt-adgroup-media.list", form);

        if (CheckUtils.isEmpty(list)) {
            return page;
        }

        for (RPTOrderMediaEntity entity : list) {

            RPTMediaData data = new RPTMediaData();

            data.setSource(entity.getSource());
            data.setDate(entity.getDate());
            data.setOrderId(entity.getOrderId());
            data.setAdid(entity.getAdid());
            data.setName(entity.getName());

            data.setReq(entity.getRequests());
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());
            data.setClick_rate(entity.getClick_rate());
            data.setEcpm(entity.getEcpm());

            page.getData().add(data);
        }

        return page;
    }

    public Page<RPTBannerData> banner(RPTOrderDimensionForm form) {

        Page<RPTBannerData> page = form.getPage();

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return page;
        }

        int count = dao.count("rpt-adgroup-banner.count", form);

        if (count == 0) {
            return page;
        }

        page.setCount(count);
        page.execute();

        List<RPTOrderBannerEntity> list = dao.getList("rpt-adgroup-banner.list", form);

        if (CheckUtils.isEmpty(list)) {
            return page;
        }

        for (RPTOrderBannerEntity entity : list) {

            RPTBannerData data = new RPTBannerData();

            data.setDate(entity.getDate());
            data.setBanner_id(entity.getBannerId());
            data.setName(entity.getName());

            data.setReq(entity.getRequests());
            data.setShow(entity.getShows());
            data.setClick(entity.getClicks());
            data.setRevenue(entity.getRevenues());
            data.setClick_rate(entity.getClick_rate());
            data.setEcpm(entity.getEcpm());

            page.getData().add(data);
        }

        return page;
    }

    public void excel(HttpServletResponse response, RPTOrderDimensionForm form) {

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return;
        }

        Date now = new Date();

        String ext = ".xlsx";
        String filePath = PropertiesUtils.getProperty("/config.properties", "tmp.dir");
        String fileName = DateUtils.format(now, DateUtils.PATTERN_yyyyMMddHHmmssS) + ext;

        Write write = new Excel2007Write(filePath, fileName);

        AdGroup adgroup = dao.get(MAPPER_KEY + ".get", form.getId());

        Map<XSSFSheet, List<? extends RPTData>> map = new LinkedHashMap<>();

        form.setPage(new Page<RPTData>());
        form.getPage().setLimit(false);
        form.getPage().setSize(Integer.MAX_VALUE);
        map.put(write.createSheet("策略概览"), daily(form).getData());

        // 地域
        if (NumberUtils.parseInt(account.getType(), -1) == 1) {
            form.setPage(new Page<RPTLocationData>());
            form.getPage().setLimit(false);
            form.getPage().setSize(Integer.MAX_VALUE);

            List<RPTLocationData> data = location(form).getData();
            map.put(write.createSheet("地域"), data);
            map.put(write.createSheet("地域汇总"), locationSum(data));
        }

        if (NumberUtils.parseInt(account.getType(), -1) == 1) {
            form.setPage(new Page<RPTHourData>());
            form.getPage().setLimit(false);
            form.getPage().setSize(Integer.MAX_VALUE);

            List<RPTHourData> data = hour(form).getData();
            map.put(write.createSheet("小时"), data);
            map.put(write.createSheet("小时汇总"), hourSum(data));
        }

        form.setPage(new Page<RPTBannerData>());
        form.getPage().setLimit(false);
        form.getPage().setSize(Integer.MAX_VALUE);

        List<RPTBannerData> tmp = banner(form).getData();
        map.put(write.createSheet("创意"), tmp);
        map.put(write.createSheet("创意汇总"), bannerSum(tmp));

        if (NumberUtils.parseInt(account.getType(), -1) == 1) {
            form.setPage(new Page<RPTMediaData>());
            form.getPage().setLimit(false);
            form.getPage().setSize(Integer.MAX_VALUE);

            List<RPTMediaData> tmp1 = media(form).getData();
            map.put(write.createSheet("媒体"), tmp1);
            map.put(write.createSheet("媒体汇总"), mediaSummation(tmp1));
        }

        RPTExcelStyle style = new RPTExcelStyle(write.getWorkbook());

        for (Entry<XSSFSheet, List<? extends RPTData>> entry : map.entrySet()) {

            XSSFSheet sheet = entry.getKey();

            // 策略概况
            write.setValue(sheet, 1, 1, "策略概况", style.title0());
            write.setValue(sheet, 1, 2, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 3, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 4, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 5, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 6, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 7, Constants.BLANK, style.title0());
            write.setValue(sheet, 1, 8, Constants.BLANK, style.title0());

            write.setValue(sheet, 2, 1, "策略名称", style.title1());
            write.setValue(sheet, 2, 2, Constants.BLANK, style.title1());
            write.setValue(sheet, 2, 3, "策略ID", style.title1());
            write.setValue(sheet, 2, 4, "策略档期", style.title1());
            write.setValue(sheet, 2, 5, Constants.BLANK, style.title1());
            write.setValue(sheet, 2, 6, Constants.BLANK, style.title1());
            write.setValue(sheet, 2, 7, "策略总预算", style.title1());
            write.setValue(sheet, 2, 8, "策略日预算", style.title1());

            write.setValue(sheet, 3, 1, adgroup.getAdGroupName(), style.content_String());
            write.setValue(sheet, 3, 2, Constants.BLANK, style.title1());
            write.setValue(sheet, 3, 3, adgroup.getId(), style.content_int());
            write.setValue(sheet, 3, 4, DateUtils.format(adgroup.getStartDate(), DateUtils.PATTERN_yyyy_MM_dd) + "~"
                    + DateUtils.format(adgroup.getEndDate(), DateUtils.PATTERN_yyyy_MM_dd), style.content_String());
            write.setValue(sheet, 3, 5, Constants.BLANK, style.title1());
            write.setValue(sheet, 3, 6, Constants.BLANK, style.title1());
            write.setValue(sheet, 3, 7, adgroup.getTotalBudget(), style.content_double());
            write.setValue(sheet, 3, 8, adgroup.getDailyBudget(), style.content_double());

            write.setValue(sheet, 5, 1, sheet.getSheetName(), style.title1());
            write.setValue(sheet, 5, 2, Constants.BLANK, style.title1());
            write.setValue(sheet, 5, 3, Constants.BLANK, style.title1());
            write.setValue(sheet, 5, 4, "曝光", style.title1());
            write.setValue(sheet, 5, 5, "点击", style.title1());
            write.setValue(sheet, 5, 7, "花费", style.title1());
            write.setValue(sheet, 5, 8, Constants.BLANK, style.title1());

            write.setValue(sheet, 6, 1, Constants.BLANK, style.title1());
            write.setValue(sheet, 6, 2, Constants.BLANK, style.title1());
            write.setValue(sheet, 6, 3, Constants.BLANK, style.title1());
            write.setValue(sheet, 6, 4, "曝光数", style.title1());
            write.setValue(sheet, 6, 5, "点击数", style.title1());
            write.setValue(sheet, 6, 6, "点击率（%）", style.title1());
            write.setValue(sheet, 6, 7, "eCPM（元）", style.title1());
            write.setValue(sheet, 6, 8, "花费（元）", style.title1());

            int rowIdx = 7;

            for (RPTData data : entry.getValue()) {

                write.setValue(sheet, rowIdx, 1, data.getDate(), style.content_String());
                write.setValue(sheet, rowIdx, 2, context(data), style.content_String());
                write.setValue(sheet, rowIdx, 3, Constants.BLANK, style.content_String());
                write.setValue(sheet, rowIdx, 4, data.getShow(), style.content_int());
                write.setValue(sheet, rowIdx, 5, data.getClick(), style.content_int());
                write.setValue(sheet, rowIdx, 6, data.getClick_rate() * 100.0, style.content_double());
                write.setValue(sheet, rowIdx, 7, data.getEcpm(), style.content_double());
                write.setValue(sheet, rowIdx, 8, data.getRevenue(), style.content_double());

                if (data instanceof RPTLocationData || data instanceof RPTHourData || data instanceof RPTBannerData || data instanceof RPTMediaData) {
                    write.addMergedRegion(sheet, rowIdx, rowIdx, 2, 3);
                } else {
                    write.addMergedRegion(sheet, rowIdx, rowIdx, 1, 3);
                }

                rowIdx++;
            }

            write.setColumnWidth(sheet, 0, 640);
            write.setHeightInPoints(sheet, 1, rowIdx, 20);

            // 合并单元格
            write.addMergedRegion(sheet, 2, 2, 1, 2);
            write.addMergedRegion(sheet, 2, 2, 4, 6);
            write.addMergedRegion(sheet, 3, 3, 1, 2);
            write.addMergedRegion(sheet, 3, 3, 4, 6);
            write.addMergedRegion(sheet, 5, 6, 1, 3);
            write.addMergedRegion(sheet, 5, 5, 5, 6);
            write.addMergedRegion(sheet, 5, 5, 7, 8);

            RegionUtil.setBorderTop(BorderStyle.THIN, new CellRangeAddress(1, 1, 1, 8), sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(3, 3, 1, 8), sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, new CellRangeAddress(1, 3, 1, 1), sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(1, 3, 8, 8), sheet);

            int bottom = entry.getValue().size() + 6;
            RegionUtil.setBorderTop(BorderStyle.THIN, new CellRangeAddress(5, 5, 1, 8), sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(bottom, bottom, 1, 8), sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, new CellRangeAddress(5, bottom, 1, 1), sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(5, bottom, 8, 8), sheet);
        }

        write.write();
        write.closeWorkbook();

        String showName = "策略报表_" + DateUtils.format(now, DateUtils.PATTERN_yyyyMMdd_HHmmss) + ext;
        fileService.download(response, new DownloadBean(filePath, fileName, showName));
    }

    private String context(RPTData data) {

        if (data instanceof RPTLocationData) {

            RPTLocationData o = (RPTLocationData) data;
            return o.getProvince() + "." + o.getCity();
        }

        if (data instanceof RPTHourData) {

            RPTHourData o = (RPTHourData) data;
            return o.getHour();
        }

        if (data instanceof RPTBannerData) {

            RPTBannerData o = (RPTBannerData) data;
            return o.getName();
        }

        if (data instanceof RPTMediaData) {

            RPTMediaData o = (RPTMediaData) data;
            return o.getSource() + " - " + o.getAdid() + " - " + o.getName();
        }

        return Constants.BLANK;
    }

    public void excelList(HttpServletResponse response, RPTAdgroupForm form) {

        Account account = accountService.getSessionUser();

        if (CheckUtils.isNull(account)) {
            return;
        }

        Date now = new Date();

        String ext = ".xlsx";
        String filePath = PropertiesUtils.getProperty("/config.properties", "tmp.dir");
        String fileName = DateUtils.format(now, DateUtils.PATTERN_yyyyMMddHHmmssS) + ext;

        Write write = new Excel2007Write(filePath, fileName);

        form.setPage(new Page<RPTData>());
        form.getPage().setLimit(false);
        form.getPage().setSize(Integer.MAX_VALUE);

        XSSFSheet sheet = write.createSheet("策略列表");
        List<RPTData> data = list(form).getData();

        RPTExcelStyle style = new RPTExcelStyle(write.getWorkbook());

        // 订单概况
        write.setValue(sheet, 1, 1, sheet.getSheetName(), style.title1());
        write.setValue(sheet, 1, 2, Constants.BLANK, style.title1());
        write.setValue(sheet, 1, 3, Constants.BLANK, style.title1());
        write.setValue(sheet, 1, 4, "曝光", style.title1());
        write.setValue(sheet, 1, 5, "点击", style.title1());
        write.setValue(sheet, 1, 7, "花费", style.title1());
        write.setValue(sheet, 1, 8, Constants.BLANK, style.title1());

        write.setValue(sheet, 2, 1, Constants.BLANK, style.title1());
        write.setValue(sheet, 2, 2, Constants.BLANK, style.title1());
        write.setValue(sheet, 2, 3, Constants.BLANK, style.title1());
        write.setValue(sheet, 2, 4, "曝光数", style.title1());
        write.setValue(sheet, 2, 5, "点击数", style.title1());
        write.setValue(sheet, 2, 6, "点击率（%）", style.title1());
        write.setValue(sheet, 2, 7, "eCPM（元）", style.title1());
        write.setValue(sheet, 2, 8, "花费（元）", style.title1());

        int rowIdx = 3;

        for (RPTData d : data) {

            write.setValue(sheet, rowIdx, 1, d.getName(), style.content_String());
            write.setValue(sheet, rowIdx, 2, Constants.BLANK, style.content_String());
            write.setValue(sheet, rowIdx, 3, Constants.BLANK, style.content_String());
            write.setValue(sheet, rowIdx, 4, d.getShow(), style.content_int());
            write.setValue(sheet, rowIdx, 5, d.getClick(), style.content_int());
            write.setValue(sheet, rowIdx, 6, d.getClick_rate() * 100.0, style.content_double());
            write.setValue(sheet, rowIdx, 7, d.getEcpm(), style.content_double());
            write.setValue(sheet, rowIdx, 8, d.getRevenue(), style.content_double());

            write.addMergedRegion(sheet, rowIdx, rowIdx, 1, 3);

            rowIdx++;
        }

        write.setColumnWidth(sheet, 0, 640);
        write.setHeightInPoints(sheet, 1, rowIdx, 20);

        // 合并单元格
        write.addMergedRegion(sheet, 1, 1, 1, 3);
        write.addMergedRegion(sheet, 1, 1, 5, 6);
        write.addMergedRegion(sheet, 1, 1, 7, 8);
        write.addMergedRegion(sheet, 2, 2, 1, 3);

        RegionUtil.setBorderTop(BorderStyle.THIN, new CellRangeAddress(1, 1, 1, 8), sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(3, 3, 1, 8), sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, new CellRangeAddress(1, 3, 1, 1), sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(1, 3, 8, 8), sheet);

        int bottom = data.size() + 6;
        RegionUtil.setBorderTop(BorderStyle.THIN, new CellRangeAddress(5, 5, 1, 8), sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(bottom, bottom, 1, 8), sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, new CellRangeAddress(5, bottom, 1, 1), sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(5, bottom, 8, 8), sheet);

        write.write();
        write.closeWorkbook();

        String showName = "策略列表_" + DateUtils.format(now, DateUtils.PATTERN_yyyyMMdd_HHmmss) + ext;
        fileService.download(response, new DownloadBean(filePath, fileName, showName));
    }

    private List<RPTLocationData> locationSum(List<RPTLocationData> data) {

        if (CheckUtils.isEmpty(data)) {
            return new ArrayList<>();
        }

        Collections.sort(data, new Comparator<RPTLocationData>() {
            public int compare(RPTLocationData o1, RPTLocationData o2) {
                return (o1.getProvince() + "-" + o1.getCity()).compareTo(o2.getProvince() + "-" + o2.getCity());
            }
        });

        List<RPTLocationData> result = new ArrayList<>();

        RPTLocationData tmp = null;

        String oldKey = "-1";

        for (RPTLocationData entity : data) {

            String newKey = entity.getProvince() + "-" + entity.getCity();

            if (!newKey.equals(oldKey)) {

                if (tmp != null) {
                    result.add(tmp);
                }

                oldKey = newKey;
                tmp = new RPTLocationData();
                tmp.setId(entity.getId());
                tmp.setProvince(entity.getProvince());
                tmp.setCity(entity.getCity());
            }

            tmp.setShow(tmp.getShow() + entity.getShow());
            tmp.setClick(tmp.getClick() + entity.getClick());
            tmp.setRevenue(tmp.getRevenue() + entity.getRevenue());
        }

        if (tmp != null) {
            result.add(tmp);
        }

        xxxx(result);

        return result;
    }

    private List<RPTHourData> hourSum(List<RPTHourData> data) {

        if (CheckUtils.isEmpty(data)) {
            return new ArrayList<>();
        }

        Collections.sort(data, new Comparator<RPTHourData>() {
            public int compare(RPTHourData o1, RPTHourData o2) {
                return o1.getHour().compareTo(o2.getHour());
            }
        });

        List<RPTHourData> result = new ArrayList<>();

        RPTHourData tmp = null;

        String oldKey = "-1";

        for (RPTHourData entity : data) {

            String newKey = entity.getHour();

            if (!newKey.equals(oldKey)) {

                if (tmp != null) {
                    result.add(tmp);
                }

                oldKey = newKey;
                tmp = new RPTHourData();
                tmp.setId(entity.getId());
                tmp.setHour(entity.getHour());
            }

            tmp.setShow(tmp.getShow() + entity.getShow());
            tmp.setClick(tmp.getClick() + entity.getClick());
            tmp.setRevenue(tmp.getRevenue() + entity.getRevenue());
        }

        if (tmp != null) {
            result.add(tmp);
        }

        xxxx(result);

        return result;
    }

    private List<RPTBannerData> bannerSum(List<RPTBannerData> data) {

        if (CheckUtils.isEmpty(data)) {
            return new ArrayList<>();
        }

        Collections.sort(data, new Comparator<RPTBannerData>() {
            public int compare(RPTBannerData o1, RPTBannerData o2) {
                return o1.getName().compareTo(o2.getName());
            }
        });

        List<RPTBannerData> result = new ArrayList<>();

        RPTBannerData tmp = null;

        String oldKey = "-1";

        for (RPTBannerData entity : data) {

            String newKey = entity.getName();

            if (!newKey.equals(oldKey)) {

                if (tmp != null) {
                    result.add(tmp);
                }

                oldKey = newKey;
                tmp = new RPTBannerData();
                tmp.setId(entity.getId());
                tmp.setName(entity.getName());
            }

            tmp.setShow(tmp.getShow() + entity.getShow());
            tmp.setClick(tmp.getClick() + entity.getClick());
            tmp.setRevenue(tmp.getRevenue() + entity.getRevenue());
        }

        if (tmp != null) {
            result.add(tmp);
        }

        xxxx(result);

        return result;
    }

    private List<RPTMediaData> mediaSummation(List<RPTMediaData> data) {

        if (CheckUtils.isEmpty(data)) {
            return new ArrayList<>();
        }

        Collections.sort(data, new Comparator<RPTMediaData>() {
            public int compare(RPTMediaData o1, RPTMediaData o2) {
                return (o1.getSource() + "-" + o1.getName() + "-" + o1.getAdid()).compareTo(o2.getSource() + "-" + o2.getName() + "-" + o2.getAdid());
            }
        });

        List<RPTMediaData> result = new ArrayList<>();

        RPTMediaData tmp = null;

        String oldKey = "-1";

        for (RPTMediaData entity : data) {

            String newKey = entity.getSource() + "-" + entity.getName() + "-" + entity.getAdid();

            if (!newKey.equals(oldKey)) {

                if (tmp != null) {
                    result.add(tmp);
                }

                oldKey = newKey;
                tmp = new RPTMediaData();
                tmp.setId(entity.getId());
                tmp.setName(entity.getName());
                tmp.setSource(entity.getSource());
                tmp.setAdid(entity.getAdid());
            }

            tmp.setShow(tmp.getShow() + entity.getShow());
            tmp.setClick(tmp.getClick() + entity.getClick());
            tmp.setRevenue(tmp.getRevenue() + entity.getRevenue());
        }

        if (tmp != null) {
            result.add(tmp);
        }

        xxxx(result);

        return result;
    }

    private void xxxx(List<? extends RPTData> result) {

        for (RPTData d : result) {

            if (d.getShow() != 0) {
                d.setClick_rate(d.getClick() * 1.0 / d.getShow());
                d.setEcpm(d.getRevenue() * 1000.0 / d.getShow());
            }
        }
    }
}